using System;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Text;

using Kenly.DBFramework.Gateway;

namespace Kenly.DBFramework.Reflection
{
    /// <summary>
    /// Mapping the properties to sql statements .
    /// </summary>
    internal class OracleCommandReflector : CommandReflector
    {

        public override string Exists(Type entityType, string condition)
        {
            ValidateType(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }
            return string.Concat("SELECT CASE WHEN EXISTS(SELECT * FROM " , AttributeUtility.ReflectTableName(entityType),
            " WHERE " , condition,")THEN 1 ELSE 0 END CASE FROM DUAL ");
        }


        public override string SelectNextIdentity(Type entityType)
        {
            ValidateType(entityType);
            TableAttribute tableAttribute = AttributeUtility.GetTableAttribute(entityType);
            if (tableAttribute==null || string.IsNullOrEmpty(tableAttribute.TableName))
            {
                throw new GatewayException(MsgResource.InvalidEntityConfig, entityType);
            }
            return string.Concat("SELECT ", tableAttribute.IdSequence, ".NEXTVAL FROM DUAL");
        }

        public override string Insert(Type entityType, bool returnIdentity, List<string> propertyNames)
        {
            return Insert(entityType,propertyNames);
        }


        public static string Insert(Type entityType, List<string> propertyNames)
        {
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                throw new GatewayException(MsgResource.InvalidArguments, "List<string> propertyNames");
            }

            ValidateType(entityType);
            TableAttribute tableAttribute = AttributeUtility.GetTableAttribute(entityType);
            string identityName = IdentityUtility.GetIdentityName(entityType);
            string columnNameList = ColumnMapper.InsertColumnNameList(entityType, propertyNames);//not including indentity
            string parameterList = ColumnMapper.InsertParameterList(DatabaseType.Oracle, entityType, propertyNames);//not including indentity
            if (string.IsNullOrEmpty(tableAttribute.TableName) || string.IsNullOrEmpty(columnNameList) || string.IsNullOrEmpty(parameterList))
            {
                throw new GatewayException(MsgResource.InvalidEntityConfig, entityType);
            }

            if (!string.IsNullOrEmpty(identityName) && string.IsNullOrEmpty(tableAttribute.IdSequence))
            {
                throw new GatewayException(MsgResource.OracleSequenceMissing, entityType.ToString());
            }

            //inserts a row
            if (string.IsNullOrEmpty(identityName) || string.IsNullOrEmpty(tableAttribute.IdSequence))
            {
                //the entity has no identity column.
                return string.Concat("INSERT INTO " , tableAttribute.TableName , "(" , columnNameList , ")VALUES(" , parameterList , ")");
            }
            else
            {
                //the entity gets identity column.
                return string.Concat("INSERT INTO " , tableAttribute.TableName , "(" + identityName , "," , columnNameList ,
                    ")VALUES( :", identityName, ",", parameterList, ")");
            }

        }

     
        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="condition"></param>
        /// <param name="topCount"></param>
        /// <param name="order"></param>
        /// <param name="propertyNames"></param>
        /// <returns></returns>
        public override string SelectTop(Type entityType, string condition, int topCount, Order order, List<string> propertyNames)
        {
            ValidateType(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }
            StringBuilder sql = new StringBuilder();
            string tableName = AttributeUtility.ReflectTableName(entityType);

            sql.Append("SELECT ");
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                sql.Append(ColumnMapper.SelectAllColumnNameList(entityType));
            }
            else
            {
                sql.Append(ColumnMapper.SelectColumnNameList(entityType, propertyNames));
            }
            sql.Append(" FROM " + tableName);
            sql.Append(" WHERE " + condition);
            if (order != null && !string.IsNullOrEmpty(order.OrderString))
            {
                sql.Append(" ORDER BY " + order.OrderString);
            }

            return topCount > 0 ? "SELECT * FROM (" + sql.ToString() + ") WHERE RowNum<" + (topCount+1) : sql.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="condition"></param>
        /// <param name="order"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="propertyNames"></param>
        /// <returns></returns>
        public override string SelectPaging(Type entityType, string condition, Order order, int pageSize, int pageIndex, List<string> propertyNames)
        {
            if (entityType == null)
            {
                throw new GatewayException(MsgResource.EmptyEntity);
            }
            if (order == null)
            {
                throw new GatewayException(MsgResource.SortedOrderRequired);
            }

            if (pageIndex < 0)
            {
                pageIndex = 0;
            }
            string tableName = AttributeUtility.ReflectTableName(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            string columnNameList = null;
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                columnNameList = ColumnMapper.SelectAllColumnNameList(entityType);
            }
            else
            {
                columnNameList = ColumnMapper.SelectColumnNameList(entityType, propertyNames);
            }

            StringBuilder statement = new StringBuilder();
            statement.Append("SELECT * FROM( SELECT ROWNUM RowNumber," + columnNameList);
            statement.Append(" FROM(SELECT " + columnNameList + " FROM " + tableName + " WHERE " + condition);
            if(order!=null && !string.IsNullOrEmpty(order.OrderString))
            {
                statement.Append(" ORDER BY " + order.OrderString );
            }
            statement.Append("))WHERE RowNumber <=" + (pageIndex + 1) * pageSize + " AND RowNumber >" + pageIndex * pageSize);
            return statement.ToString();
        }


        protected override string GetUpdateColumnParameterPairs(Type entityType, bool updatedByPK,List<string> propertyNames)
        {
            return ColumnMapper.UpdateColumnParameterPairs(DatabaseType.Oracle, entityType, updatedByPK,propertyNames);
        }
    }
}
